using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;

namespace Log2SQL
{
    public enum SQLServerAuthMode
    {
        WindowsAuthentication = 1,
        SQLAuthentication
    }

    public enum ImportMode
    {
        SQLImport = 1,
        GenerateScript,
        MDBImport
    }

    public class LogImport
    {
        protected DbConnection _cn;
        protected DbCommand _cmd;

        //protected SqlConnection _cn;
        //protected SqlCommand _cmd;

        //protected OleDbCommand _oledbCmd;
        //protected OleDbConnection _oledbCN;

        private StreamWriter _scriptStreamWriter = null;

        protected bool _isAborted;

        private bool _putAllInOneTable = true;
        public bool PutAllInOneTable
        {
            get { return _putAllInOneTable; }
            set { _putAllInOneTable = value; }
        }

        private string _sqlServerName = "";
        public string SQLServerName
        {
            get { return _sqlServerName; }
            set { _sqlServerName = value; }
        }

        private string _databaseName = "";
        public string DatabaseName
        {
            get { return _databaseName; }
            set { _databaseName = value; }
        }
        
        protected string _tableNameToWriteTo = "";
        protected bool _isTableCreated = false;

        private string _desiredTableName = "";
        public string DesiredTableName
        {
            get { return _desiredTableName; }
            set { _desiredTableName = value; }
        }
	
        private string _sqlPassword = "";
        public string SQLPassword
        {
            get { return _sqlPassword; }
            set { _sqlPassword = value; }
        }

        protected ImportMode _logImportMode = ImportMode.SQLImport;
        public ImportMode LogImportMode
        {
            get { return _logImportMode; }
            set { _logImportMode = value; }
        }

        private string _sqlScriptPath;
        public string SQLScriptPath
        {
            get { return _sqlScriptPath; }
            set { _sqlScriptPath = value; }
        }

        private string _mdbFilePath;
        public string MDBFilePath
        {
            get { return _mdbFilePath; }
            set { _mdbFilePath = value; }
        }

        private string _sqlUserName = "";
        public string SQLUserName
        {
            get { return _sqlUserName; }
            set { _sqlUserName = value; }
        }
	
        private SQLServerAuthMode _sqlAuthMode = SQLServerAuthMode.WindowsAuthentication;
        public SQLServerAuthMode SQLAuthMode
        {
            get { return _sqlAuthMode; }
            set { _sqlAuthMode = value; }
        }

        //delegates to update progressbars
        public delegate void UpdateOverallProgressDelegate(byte currValue);
        public UpdateOverallProgressDelegate UpdateOverallProgress;

        public delegate void UpdateCurrFileProgressDelegate(byte currValue);
        public UpdateCurrFileProgressDelegate UpdateCurrFileProgress;

        //constructor
        public LogImport()
        {
        }

        public void Abort()
        {
            _isAborted = true;
        }

        private void Init()
        {
            _isAborted = false;

            if (_logImportMode == ImportMode.GenerateScript)
            {
                if (File.Exists(_sqlScriptPath))
                    File.Delete(_sqlScriptPath);
                _scriptStreamWriter = File.CreateText(_sqlScriptPath);
            }
            else if (_logImportMode == ImportMode.SQLImport)
            {
                string connStr;
                if (_sqlAuthMode == SQLServerAuthMode.SQLAuthentication)
                    connStr = "data source=" + _sqlServerName + ";initial catalog=" + _databaseName + ";User ID=" + _sqlUserName + ";Password=" + _sqlPassword;
                else
                    connStr = "data source=" + _sqlServerName + ";initial catalog=" + _databaseName + ";Integrated Security=SSPI";

                _cn = new SqlConnection(connStr);
                _cmd = new SqlCommand();
                _cmd.Connection = _cn;
            }
            else if (_logImportMode == ImportMode.MDBImport)
            {
                string connStr;
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _mdbFilePath + ";";

                _cn = new OleDbConnection(connStr);
                _cmd = new OleDbCommand();
                _cmd.Connection = _cn;
            }
        }

        //creates table with the specified fields
        protected void SQLCreateTable(string tableName, string[] fields)
        {
            string sqlStr = "CREATE TABLE " + tableName + " ( ";
            foreach (string field in fields)
            {
                sqlStr += "[" + field + "] varchar(255), ";
            }
            sqlStr = sqlStr.Substring(0, sqlStr.Length - 2);//remove the last ", "
            sqlStr += ")";
            if (_logImportMode == ImportMode.SQLImport || _logImportMode == ImportMode.MDBImport)
            {
                _cmd.CommandText = sqlStr;
                _cmd.ExecuteNonQuery();
            }
            else
            {
                _scriptStreamWriter.WriteLine();
                _scriptStreamWriter.WriteLine(sqlStr);
                _scriptStreamWriter.WriteLine();
            }
        }

        //insert values into the table
        protected void SQLInsertValues(string tableName, string[] values)
        {
            string sqlStr = "INSERT INTO " + tableName + " VALUES (";
            foreach (string value in values)
            {
                string valueToInsert = value.Replace("'", "''").Trim();
                if (valueToInsert.Length > 255) valueToInsert = valueToInsert.Substring(0, 255);
                sqlStr += "'" + valueToInsert + "', ";
            }
            sqlStr = sqlStr.Substring(0, sqlStr.Length - 2);//remove the last ", "
            sqlStr += ")";
            if (_logImportMode == ImportMode.SQLImport || _logImportMode== ImportMode.MDBImport)
            {
                _cmd.CommandText = sqlStr;
                _cmd.ExecuteNonQuery();
            }
            else
            {
                _scriptStreamWriter.WriteLine(sqlStr);
            }
        }

        /// <summary>
        /// main import functions. Itarates thwourg files and calls for ProcessLine
        /// </summary>
        /// <param name="filenames">array of filenames</param>
        /// <returns>number of rows imported</returns>
        public int Import(string[] filenames)
        {
            Init();

            int lineCounter = 0, lineSuccessCounter = 0, fileCounter = 0;

            //try to connect (if sql-importing)
            if (_logImportMode == ImportMode.SQLImport || _logImportMode == ImportMode.MDBImport)
            {
                try
                {
                    _cn.Open();
                    _cn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not open a database connection.\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return 0;
                }
                _cn.Open();
            }

            foreach (string fname in filenames)
            {
                StreamReader sr = File.OpenText(fname); //open the file

                long fileSize = sr.BaseStream.Length; //file length
                long iPos; //holds the current position in a file (for progressbar)

                InitNewFile((fileCounter==0));
                if (!_putAllInOneTable) _tableNameToWriteTo = _desiredTableName + fileCounter.ToString();
                else _tableNameToWriteTo = _desiredTableName;

                while (sr.Peek() >= 0)
                {
                    if (_isAborted) //aborting
                    {
                        sr.Close();
                        if (_logImportMode == ImportMode.SQLImport || _logImportMode == ImportMode.MDBImport)
                            _cn.Close();
                        else
                            _scriptStreamWriter.Close();
                        return lineSuccessCounter;
                    }

                    string line = sr.ReadLine(); //read next line
                    
                    //updating progress delegate
                    iPos = sr.BaseStream.Position; //current position in the stream
                    if (UpdateCurrFileProgress != null)
                    {
                        byte curpos = (byte)(100 * iPos / fileSize);
                        UpdateCurrFileProgress(curpos);
                    }
                    lineCounter++;

                    try
                    {
                        ProcessLine(line);
                        lineSuccessCounter++;
                    }
                    catch (Exception ex)
                    {
                        if (!_isTableCreated) //table not created yet - show error msg and exit
                        {
                            MessageBox.Show("Error initializinf log file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            Abort();
                        }
                        else if (MessageBox.Show("Error processing line " + lineCounter + ". Corrupt log file or wrong format.\r\n\r\nError message:" + ex.Message + "\r\n\r\nClick Cancel to go the next line or OK to abort", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
                        {
                            Abort();
                        }
                    }

                    Application.DoEvents();
                }
                fileCounter++;
                sr.Close();

                //updating overall progressbar
                if (UpdateOverallProgress != null)
                {
                    byte curpos = (byte)Math.Round((double)(100 * fileCounter / filenames.Length));
                    UpdateOverallProgress(curpos);
                }

            }
            if (_logImportMode == ImportMode.SQLImport || _logImportMode== ImportMode.MDBImport)
                _cn.Close();
            else
                _scriptStreamWriter.Close();
            return lineSuccessCounter;
        }

        protected virtual void ProcessLine(string line)
        {
        }

        private void InitNewFile(bool isFirstFile)
        {
            if (isFirstFile)
                _isTableCreated = false;
            else
                _isTableCreated = _putAllInOneTable;
        }
    }
}
